package com.data.dao;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;
import org.springframework.stereotype.Repository;

import com.base.dao.TemplateDaoImpl;
import com.data.entity.ChargeMonth;

@Repository("chargeMonthDao")
public class ChargeMonthDaoImpl extends TemplateDaoImpl<ChargeMonth, Integer>implements ChargeMonthDao{
	/**
	 * 根据年月查询电费月记录
	 * @param id生产线或者设备id
	 * @param belongDate
	 * @param chargeName
	 * @param  isLine
	 * @return
	 */
	@Override
	public ChargeMonth getChargeMonth(int id, String belongDate,String chargeName,String isLine) {
		StringBuilder sb=new StringBuilder("select * from chargeMonth where ");
		if(isLine.equals("Y")){
			sb.append(" lineId=:id ");
		}else{
			sb.append(" equId=:id ");
		}
		sb.append("  and CONVERT(VARCHAR(7),belongDate,120)=:belongDate and chargeName=:chargeName");
		
//		String  sql="select * from chargeMonth where equId=:equId and CONVERT(VARCHAR(7),belongDate,120)=:belongDate and chargeName=:chargeName";
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("id", id);
		params.put("belongDate", belongDate);
		params.put("chargeName", chargeName);
		return jt.queryForObject(sb.toString(), params, ParameterizedBeanPropertyRowMapper.newInstance(ChargeMonth.class));
	}

	/**
	 * 获取时间区间内的所有chargeName
	 * @param startMonth(如：2016-07)
	 * @param endMonth(如：2016-08)
	 * @return
	 */
	@Override
	public List<ChargeMonth> getDistinctChargeName(String startMonth,
			String endMonth) {
		StringBuffer sql=new StringBuffer("select distinct(chargeName) chargeName from chargeMonth  where CONVERT(VARCHAR(7),belongDate,120)>=:startMonth and CONVERT(VARCHAR(7),belongDate,120)<=:endMonth");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("endMonth", endMonth);
		params.put("startMonth", startMonth);
		return jt.query(sql.toString(),params,ParameterizedBeanPropertyRowMapper.newInstance(ChargeMonth.class));
	}


	/**
	 * 根据类型，时间，equid找到累计的电度电费
	 * @param id
	 * @param startTime:2016-07
	 * @param endTime:2016-08
	 * @param chargeName
	 * @param isLine:Y/N
	 * @return
	 */
	@Override
	public ChargeMonth getDurationByChargeNameAndDate(int id,
			String startTime,String endTime, String chargeName, String isLine) {
		StringBuilder sb=new StringBuilder("select COALESCE(SUM(relativeComp),0) relativeComp,COALESCE(SUM(charge),0) charge from chargeMonth where ");
		Map<String, Object> params=new HashMap<String, Object>();
		if(isLine.equals("Y")){
			sb.append(" lineId=:id ");
		}else{
			sb.append(" equId=:id ");
		}
		if(chargeName!=null&&!chargeName.equals("")){
			sb.append(" and chargeName=:chargeName ");
			params.put("chargeName", chargeName);
		}
		sb.append("  and CONVERT(VARCHAR(7),belongDate,120)>=:startTime  and CONVERT(VARCHAR(7),belongDate,120)<=:endTime ");
		params.put("id", id);
		params.put("startTime", startTime);
		params.put("endTime", endTime);
		return jt.queryForObject(sb.toString(), params, ParameterizedBeanPropertyRowMapper.newInstance(ChargeMonth.class));
	
	}

	/**
	 * 根据belongWeek找到该周使用的总电度
	 * @param id
	 * @param belongDate:2016-08
	 * @param chargeName:可有可无
	 * @param isLine：Y/N
	 * @return
	 */
	@Override
	public ChargeMonth getCompByDate(int id, String belongDate,String chargeName,String isLine) {
		StringBuilder sb=new StringBuilder("select COALESCE(SUM(relativeComp),0) relativeComp,COALESCE(SUM(charge),0) charge  from chargeMonth where ");
		Map<String, Object> params=new HashMap<String, Object>();
		if(isLine.equals("Y")){
			sb.append(" lineId=:id ");
		}else{
			sb.append(" equId=:id ");
		}
		if(chargeName!=null&&!chargeName.equals("")){
			sb.append(" and chargeName=:chargeName");
			params.put("chargeName", chargeName);
		}
		sb.append("  and CONVERT(VARCHAR(7),belongDate,120)=:belongDate  ");
		params.put("id", id);
		params.put("belongDate", belongDate);
		return jt.queryForObject(sb.toString(), params, ParameterizedBeanPropertyRowMapper.newInstance(ChargeMonth.class));
	}

	/**
	 * 查询数据库里的相同月份下的所有不同日期(belongDate)
	 * @param id
	 * @param month:08
	 * @return
	 */
	@Override
	public List<ChargeMonth> getdistinctDate(int id, String month,String isLine) {
		StringBuilder sb=new StringBuilder("select distinct(belongDate) belongDate from chargeMonth where ");
		if(isLine.equals("Y")){
			sb.append(" lineId=:id ");
		}else{
			sb.append(" equId=:id ");
		}
		sb.append(" and SUBSTRING(Convert(varchar(10),belongDate,112),6,2)=:month");
//		String  sql="select distinct(belongDate) belongDate from chargeMonth where equId=:equId and SUBSTRING(Convert(varchar(10),belongDate,112),6,2)=:month ";
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("id", id);
		params.put("month", month);
		return jt.query(sb.toString(), params, ParameterizedBeanPropertyRowMapper.newInstance(ChargeMonth.class));
	}
	
}
